package org.texteasy;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;

import org.texteasy.helpers.TextMessageToContact;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.DatabaseUtils.InsertHelper;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TextEasyDBAdapter {

	private static final String TAG = "TextEasyDBAdapter";
	
	public static final String KEY_ROWID = "_id";
	public static final String KEY_SELECTED_ID = "contactid";
	public static final String KEY_MESSAGE = "message";
	public static final String KEY_CREATED_DATE = "created_date";
	public static final String KEY_MESSAGE_CONTACT_ID = "contact_id";
	public static final String KEY_MESSAGE_ID = "message_id";
	public static final String KEY_FINISHED_DATE = "finished_date";
	public static final String KEY_SENT_STATUS = "sent_status";
	
	private static final String DATABASE_NAME = "easy_text";
    private static final String DATABASE_SELECTED_IDS_TABLE = "selected_ids";
    private static final String DATABASE_MESSAGES_TABLE="messages";
    private static final String DATABASE_MESSAGE_CONTACTS_TABLE="message_contacts";
    private static final int DATABASE_VERSION = 3;
	
	private DatabaseHelper dbHelper;
	
    private final Context context;
    
    private static class DatabaseHelper extends SQLiteOpenHelper{
    	
    	DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
    	
    	@Override
    	public void onCreate(SQLiteDatabase db) {
    		db.execSQL("create table selected_ids (_id integer primary key autoincrement," +
				"contactid long not null);");
    		db.execSQL("create table messages(_id integer primary key autoincrement," +
				"message string not null, created_date not null, finished_date);");
    		db.execSQL("create table message_contacts(_id integer primary key autoincrement," +
				"message_id long not null, contact_id long not null, sent_status )");
    	}
    	
    	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    		db.execSQL("DROP TABLE IF EXISTS selected_ids");
            onCreate(db);
    	}
    	
    }
    
    public TextEasyDBAdapter(Context context){
    	
    	if(context == null){
    		throw new IllegalArgumentException("This method does not accept " +
				"null parameters");
    	}
    	this.context = context;
    }
    
    /**
     * Open the selected_ids database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public TextEasyDBAdapter open() throws SQLException {
        dbHelper = new DatabaseHelper(this.context);
        return this;
    }
    
    public boolean isOpen(){
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	return database.isOpen();
    }
    
    public void close() {
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
        database.close();
        dbHelper.close();
    }
    
    public void persistSelectedIds(ArrayList<Long> selectedIds){
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	InsertHelper insertHelper = new InsertHelper(database, DATABASE_SELECTED_IDS_TABLE);
    	database.beginTransaction();
    	try{
	    	ContentValues initialValues = null;
	    	for(Long contactId: selectedIds){
	    		initialValues = new ContentValues();
	    		initialValues.put(KEY_SELECTED_ID, contactId);
	    		insertHelper.insert(initialValues);
	    	}
	    	database.setTransactionSuccessful();
    	}finally{
    		database.endTransaction();
    	}
    	
    	database.close();
    }
    
    public int clearSelectedContacts(){
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	int count = database.delete(DATABASE_SELECTED_IDS_TABLE, null, null);
    	database.close();
    	return count;
    }
    
    public Cursor getSelectedIds(){
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	return database.query(DATABASE_SELECTED_IDS_TABLE, 
    			new String[]{KEY_SELECTED_ID}, null,null,
    			null,null,null);
    	
    }
    
    /*public HashSet<Long> getSelectedIdsAndClear(){
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	
    	Cursor cursor = database.query(DATABASE_SELECTED_IDS_TABLE, 
			new String[]{KEY_SELECTED_ID}, null,null,
			null,null,null); 
    	
    	HashSet<Long> selectedIds = new HashSet<Long>();
    	
    	while(cursor.moveToNext()){
    		selectedIds.add(cursor.getLong(cursor.
				getColumnIndexOrThrow(KEY_SELECTED_ID)));
    	}
    	database.delete(DATABASE_SELECTED_IDS_TABLE, null, null);
    	cursor.close();
    	database.close();
    	return selectedIds;
    }*/
    
    public long createNewMessage(String message, ArrayList<Long> contactIds){
    	
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    	
    	long messageId = -1;
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	InsertHelper messagesInsertHelper = new InsertHelper(database, DATABASE_MESSAGES_TABLE);
    	InsertHelper messageContactsInsertHelper = new InsertHelper(database, DATABASE_MESSAGE_CONTACTS_TABLE);
    	
    	database.beginTransaction();
    	try{
    		
    		ContentValues messageInitialValues = new ContentValues();
    		messageInitialValues.put(KEY_MESSAGE, message);
    		messageInitialValues.put(KEY_CREATED_DATE, dateFormat.format(new Date()));
    		messageId = messagesInsertHelper.insert(messageInitialValues);
    		
	    	ContentValues initialValues = null;
	    	
	    	for(long contactId: contactIds){
	    		initialValues = new ContentValues();
	    		initialValues.put(KEY_MESSAGE_CONTACT_ID, contactId);
	    		initialValues.put(KEY_MESSAGE_ID, messageId);
	    		messageContactsInsertHelper.insert(initialValues);
	    	}
	    	database.setTransactionSuccessful();
    	}finally{
    		database.endTransaction();
    	}
    	
    	database.close();
    	
    	return messageId;
    }
    
    /**
     * Returns a list of "InProgress" messages. This method is called
     * on startup to retrieve any InProgress messages to continue sending.
     * There will only be inProgress messages if the app was shutdown
     * while sending messages
     * @return
     */
    public HashSet<Long> getInProgressMessageIds(){
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	
    	Cursor cursor = database.query(DATABASE_MESSAGES_TABLE, 
			new String[]{KEY_ROWID}, KEY_FINISHED_DATE + " is null",null,
			null,null,KEY_CREATED_DATE + " ASC"); 
    	
    	HashSet<Long> inProgressMessages = new HashSet<Long>();
    	
    	while(cursor.moveToNext()){
    		inProgressMessages.add(cursor.getLong(cursor.
				getColumnIndexOrThrow(KEY_ROWID)));
    	}
    	cursor.close();
    	database.close();
    	return inProgressMessages;
    }
    
    public int setTextMessageToContactToSent(long id){
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	
    	ContentValues contentValues = new ContentValues();
    	contentValues.put(KEY_SENT_STATUS, "Y");
    	int valueToReturn = database.update(DATABASE_MESSAGE_CONTACTS_TABLE, 
    			contentValues, "_ID=?", new String[]{id + ""});
    	
    	database.close();
    	
    	return valueToReturn;
    }
    
    public void setTextMessageToSent(long messageId){
    	
    	SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	
    	ContentValues contentvalues = new ContentValues();
    	contentvalues.put(KEY_FINISHED_DATE, dateFormat.format(new Date()));
    	database.update(DATABASE_MESSAGES_TABLE, contentvalues, "_ID=?", 
			new String[]{messageId + ""});
    	
    	database.close();
    }
    
    public TextMessageToContact getNextTextMessageToContact(long messageId){
    	
    	SQLiteDatabase database = dbHelper.getWritableDatabase();
    	
    	Cursor cursor = database.query(DATABASE_MESSAGES_TABLE, 
    			new String[]{KEY_MESSAGE}, KEY_ROWID + "=?",new String[]{messageId + ""},
    			null,null,null);
    	String textMessage = null;
    	
    	while(cursor.moveToNext()){
    		textMessage = cursor.getString(0);
    	}
    	cursor.close();
    	
    	if(textMessage == null){
    		return null;
    	}
    	cursor = database.rawQuery("SELECT _ID, CONTACT_ID FROM " +
			"MESSAGE_CONTACTS WHERE MESSAGE_ID = ? AND SENT_STATUS IS NULL ORDER BY _ID DESC LIMIT 1", 
			new String[]{messageId + ""});; 
    	
		TextMessageToContact toReturn = null;
    	while(cursor.moveToNext()){
    		long id = cursor.getLong(0);
    		long contactId = cursor.getLong(1);
    		toReturn = new TextMessageToContact(id, messageId, contactId, textMessage, false);
    	}
    	cursor.close();
    	database.close();
    	return toReturn;
    	
    }
}
